package com.labmi.experiment.dao;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NoResultException;
import javax.persistence.Query;

import com.labmi.experiment.CategoriesMappingBean;
import com.labmi.experiment.Category;
import com.labmi.experiment.Item;


public class CategoryDAO implements GenericDao<Category, Serializable> {

	
	private EntityManager em;

	public CategoryDAO() {
		em = EntityManagerSingleton.getInstance();
	}
	
	@Override
	public Serializable create(Category newInstance) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Category read(Serializable id) {
		System.out.println("Finding Category with id: " + id);
		
		Category category = null;
		try {
			category = em.find(Category.class, id);
			
			System.out.println("Found Category with id: " + id);
		} catch (NoResultException e) {
			
			System.out.println("No element found with id: " + id);
		}
		
		
		
		return category;
		
	}

	@Override
	public void update(Category transientObject) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void delete(Category persistentObject) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Category> fetchAll(int limit) {
		System.out.println("Fetching all Categories from database...");
		
		Query queryResult = em.createQuery("SELECT x  FROM Category x");
		
		List<Category> resultList = queryResult.getResultList();
		System.out.println("Items fetched: " + resultList.size());
		for (Category aCategory : resultList) {
			System.out.println(aCategory.getName());
		}
		
		return resultList;
	}
	
	public Category getCategoryByName(String categoryName) {
		
		
		Category category = null;
		
		try 
		{
			Query query = em.createQuery("SELECT x  FROM Category x WHERE x.name = ?1").setParameter(1, categoryName);
			
			category = (Category) query.getSingleResult();
			
			System.out.println("Found Category with name: " + categoryName);
		} catch (NoResultException e) {
			
			System.out.println("No element found with name: " + categoryName);
		}
		
		
		
		return category;
	}

	
	/**
	 * Return the full category path relative to the category passed as parameter. 
	 * For example, if the parameter is the id of category_2 the returned list will contain
	 * category_2 as the last element preceded by the parent categories
	 * 
	 *  > category 0
	 *  	>category 1
	 *  		>category 2
	 *  
	 * 
	 * @param category id
	 * @return
	 */
	public List<Category> getCategoryFullPath(String categoryId) 
	{
		List<Category> categories = new ArrayList<Category>();
		
		try 
		{
			
//			String queryString = "SELECT root.id   AS root_id, " +
//			"		root.name AS root_name, " +
//			"		secondCategory.id AS secondCategory_id, " +
//			"		secondCategory.name AS secondCategory_name, " +
//			"		thirdCategory.id  AS thirdCategory_id, 	thirdCategory.name 	AS thirdCategory_name " +
//			" FROM category AS root" +
//			" LEFT OUTER JOIN category AS secondCategory ON secondCategory.parent = root.id " +
//			" LEFT OUTER JOIN category AS thirdCategory ON thirdCategory.parent = secondCategory.id " +
//			" WHERE root.parent =0";
			
			Query nativeQuery = em.createNamedQuery("singleCategoryFullPath").setParameter(1, categoryId);
			
			List<Object[]> resultList = nativeQuery.getResultList();
			
			Category category = new Category(); //first category
				
			
			for (Object[] resultSetColumns : resultList) {
				
				
				for (int i = 0; i < resultSetColumns.length; i = i+ 2) 
				{ //increment by 2!
					if(i == 0) //means first category
						category.setParent(0);
					else
						category = new Category(); //first category
					
					category.setId( (Integer) resultSetColumns[i] ) ; 	//access category id
					category.setName( (String) resultSetColumns[i+1] ); //access category idresultSetColumns[i+1]; //access category name
					
					categories.add(category);
					
					//WORKAROUND PER LA DISTINCT CHE NON FUNZIONA....
					if( ( resultSetColumns[i].toString()).equals(categoryId) ) 
						return categories;
						
					
				}
			}
			
			
		} catch (NoResultException e) {
			
			e.printStackTrace();
		}
		
		return categories;
	}
	
	
	/**
	 * 
	 * 
	 * @param categoryId
	 * @return the id of the root category relative to the category id passed as parameter
	 */
	public String getRootForThisCategory(String categoryId)
	{
		Query nativeQuery = em.createNamedQuery("calculateRootCategory").setParameter(1, categoryId);
		List<Object[]> resultList = nativeQuery.getResultList();
		
		return null;
	}
}
